package com.carlinfo.common.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

/**
 *  数据库的工具类(JDBC) ~单例模式 new的时候调用构造方法 构造方法是私有的 定义一个私有的属性,类型就是自己 获取一个对象,就是自己
 *  单例模式的好处: 省资源:此类的属性特别多;构造方法的代码特别多(创建的时候花时间);tomcat一启动就要new 缺点:
 *  一个类属性,每一个对象的值都不一样;
 */
public class DbUtil
{
	/* 驱动 */
	private String driver = ConstantFinalUtil.bundle.getString("jdbc.driver");
	/*
	 * url: http://localhost:8080/01-jsp/scope/page.jsp?a=1&b=2&c=3
	 * jdbc:mysql://localhost:3306/userscenter?useSSL=false
	 * jdbc:mysql:///userscenter?useSSL=false
	 */
	private String url = ConstantFinalUtil.bundle.getString("jdbc.url");
	/* 用户名 */
	private String user = ConstantFinalUtil.bundle.getString("jdbc.user");
	/* 密码 */
	private String password = ConstantFinalUtil.bundle.getString("jdbc.password");

	private static DbUtil INSTANCE = new DbUtil();

	/**
	 * 私有的,外面的类不允许new
	 */
	private DbUtil()
	{
		try
		{
			Class.forName(driver);
		} catch (ClassNotFoundException e)
		{
			System.out.println("==加载驱动失败==");
			e.printStackTrace();
		}
	}

	/**
	 * 获取对象 Calendar.getInstance; static:调用的时候不用new;new也new不出来
	 * 
	 * @return
	 */
	public static DbUtil getInstance()
	{
		return INSTANCE;
	}

	/**
	 * 获取链接
	 * 
	 * @return
	 */
	public Connection getConn()
	{
		try
		{
			/* 获取链接 */
			Connection connection = DriverManager.getConnection(url, user, password);
			return connection;
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 关闭链接
	 */
	public void closeAll(Connection connection, Statement st, ResultSet rs)
	{
		try
		{
			/* 倒着关 */
			if (rs != null)
			{
				rs.close();
				rs = null;
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}

		try
		{
			/* 倒着关 */
			if (st != null)
			{
				st.close();
				st = null;
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}

		try
		{
			/* 倒着关 */
			if (connection != null)
			{
				connection.close();
				connection = null;
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

	/**
	 * 标准的JDBC 加载驱动 获取连接 操作数据库 关闭链接 查询所有的朝代
	 */
	public void standardJDBC()
	{
		/* 驱动:包名+类名 */
		String driver = "com.mysql.jdbc.Driver";
		/*
		 * url: http://localhost:8080/01-jsp/scope/page.jsp?a=1&b=2&c=3
		 * jdbc:mysql://localhost:3306/userscenter?useSSL=false
		 * jdbc:mysql:///userscenter?useSSL=false
		 */
		String url = "jdbc:mysql:///userscenter?useSSL=false";
		/* 用户名 */
		String user = "root";
		/* 密码 */
		String password = "123456";
		/* sql语句 */
		String sql = "select * from a_dynasty;";

		Connection connection = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try
		{
			/* 利用反射new对象 */
			Class.forName(driver);
			/* 获取链接 */
			connection = DriverManager.getConnection(url, user, password);
			/* 准备语句对象 */
			pst = connection.prepareStatement(sql);
			/*
			 * 执行sql语句 CUD:executeUpdate,R:executeQuery
			 */
			rs = pst.executeQuery();
			/* 查询一条:if,还是查询多条:while */
			System.out.println("id\t名字\t首都\t国祚\t状态\t开国时间\t\t\t创建时间\t\t\t更新时间\t\t\t发布时间");
			while (rs.next())
			{
				/*
				 * 取值 整数:第几列 字符串:别名
				 */
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String capital = rs.getString("capital");
				short age = rs.getShort("age");
				/* 时间戳 */
				Date st = rs.getTimestamp("st");
				Date createTime = rs.getTimestamp("createTime");
				Date updateTime = rs.getTimestamp("updateTime");
				Date pubTime = rs.getTimestamp("pubTime");
				byte status = rs.getByte("status");
				/* 打印 */
				System.out.println(id + "\t" + name + "\t" + capital + "\t" + age + "\t" + status + "\t"
						+ st.toLocaleString() + "\t" + createTime.toLocaleString() + "\t" + updateTime.toLocaleString()
						+ "\t" + pubTime.toLocaleString());
			}
		} catch (ClassNotFoundException e)
		{
			e.printStackTrace();
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally
		{
			try
			{
				/* 倒着关 */
				if (rs != null)
				{
					rs.close();
					rs = null;
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}

			try
			{
				/* 倒着关 */
				if (pst != null)
				{
					pst.close();
					pst = null;
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}

			try
			{
				/* 倒着关 */
				if (connection != null)
				{
					connection.close();
					connection = null;
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
	}

	public void newJDBC()
	{
		String sql = "select * from a_dynasty;";

		DbUtil dbUtil = DbUtil.getInstance();
		/* 加载驱动+获取链接 */
		PreparedStatement pst = null;
		ResultSet rs = null;
		Connection conn = dbUtil.getConn();
		try
		{
			/* 创建语句 */
			pst = conn.prepareStatement(sql);
			rs = pst.executeQuery();
			/* 查询一条:if,还是查询多条:while */
			System.out.println("id\t名字\t首都\t国祚\t状态\t开国时间\t\t\t创建时间\t\t\t更新时间\t\t\t发布时间");
			while (rs.next())
			{
				/*
				 * 取值 整数:第几列 字符串:别名
				 */
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String capital = rs.getString("capital");
				short age = rs.getShort("age");
				String context = rs.getString("context");
				/* 时间戳 */
				Date st = rs.getTimestamp("st");
				Date createTime = rs.getTimestamp("createTime");
				Date updateTime = rs.getTimestamp("updateTime");
				Date pubTime = rs.getTimestamp("pubTime");
				byte status = rs.getByte("status");
				/* 打印 */
				System.out.println(id + "\t" + name + "\t" + capital + "\t" + age + "\t" + context + "\t" + status + "\t"
						+ st.toLocaleString() + "\t" + createTime.toLocaleString() + "\t" + updateTime.toLocaleString()
						+ "\t" + pubTime.toLocaleString());
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally
		{
			dbUtil.closeAll(conn, pst, rs);
		}
	}

	/**
	 * 测试;模拟外边的类测试
	 * 
	 * @param args
	 */
	public static void main(String[] args)
	{
		/*
		 * 创建一个对象 Calendar cal = Calendar.getInstance();
		 */
		/*
		 * for(int i = 0 ; i < 10 ; i ++) { DbUtil dbUtil = DbUtil.getInstance() ;
		 * System.out.println("==getInstance==>" + dbUtil); 在当前类里面
		 * //System.out.println("==DbUtil==>" + new DbUtil()); }
		 */
		/* 获取对象 */
		DbUtil dbUtil = DbUtil.getInstance();
		/* 普通的方法,调用的时候需要使用对象,而不是类名 */
		// dbUtil.standardJDBC();
		dbUtil.newJDBC();
	}
}
